#!/usr/bin/env node

const log=console.log,
      fs=require('fs'),
      path=require('path'),
    {con}=require('./common.js');
    const moment = require('moment');


async function changeUserInfo(id,name,sex,brief,birthday,phone){
    let sql;
    if(!sex){
        sql=`update user set username='${name}',brief='${brief}',sex=null,birthady='${birthday}',phone='${phone}' WHERE id="${id}";`;
    }
    else{
        sql=`update user set username='${name}',brief='${brief}',sex=${sex},birthady='${birthday}',phone='${phone}' WHERE id="${id}";`;
    }
    log('sql:',sql)
    let [rows]=await con.execute(sql);
    return rows
}

async function interestme(id){
    const sql=`SELECT fan.id,user.username,user.image,fan.fanTime FROM fan,user where user.id=fan.id and fan.fanId="${id}";`;
    log(sql)
    let [rows]=await con.execute(sql);
    for(let item of rows){
        let newFanTime = moment(item.fanTime).format('YYYY/MM/DD HH:mm');
        item.fanTime = newFanTime;
    }
    return rows
}

async function getotherhome(id){
    const sql=`SELECT user.id,username,brief,image,attention,sex,birthady,background,user.like,fan from user where id="${id}";`;
    let [rows]=await con.execute(sql);
    return rows[0]
}

async function changeattention(otherid,id){
    const sql=`SELECT count(id) count from fan where id="${id}" and fanId="${otherid}";`;
    let [rows]=await con.execute(sql);
    return rows[0]
}

// 在他是我的粉丝的前提下,点击关注,私信会出现快来聊天的界面
// 他是我的粉丝前提数据库中数据为：fan:id:我的 fanId:他的 attention:id:他的 attentionIdL:我的
// !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
// fan的时间和attention的时间 如：fan:1 2 time1  attention: 2 1 time2 time1==time2(手动更改数据库注意同时更改)
// 他成为我的粉丝==我成为他的关注
async function addattention(id,otherid,fanTime){
    console.log('hhhhhhhhhh')
    const sql=`insert into fan (id,fanId,fanTime) values ('${otherid}','${id}','${fanTime}');`;
    const sql2=`insert into attention (attentionId,id,attentionTime) values ('${otherid}','${id}','${fanTime}');`;
    let [rows]=await con.execute(sql);
    let [rows2]=await con.execute(sql2);
    const sqladd1=`update user set attention = attention+1 where id='${id}';`;
    let [rowsadd1]=await con.execute(sqladd1)
    const sqladd2=`update user set fan = fan+1 where id='${otherid}';`;
    let [rowsadd2]=await con.execute(sqladd2)
    
    log(sql)
    return rows
}

// !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
async function delattention(id,otherid){
    const sql=`delete from fan where id='${otherid}' and fanId='${id}';`;
    const sqladd1=`update user set attention = attention-1 where id='${id}';`;
    const sql2=`delete from attention where id='${id}' and attentionId='${otherid}';`;
    let [rows1]=await con.execute(sql2)
    let [rowsadd1]=await con.execute(sqladd1)
    const sqladd2=`update user set fan = fan-1 where id='${otherid}';`;
    let [rowsadd2]=await con.execute(sqladd2)
    log(sql)
    let [rows]=await con.execute(sql);
    return rows
}

async function getLike(id){
    const sql=`select user.like.userId,user.like.passageId,essay.title,user.like.likeTime,essayimage.img from user.like,user.essay,user.essayimage 
    where user.like.passageId=essay.id and essay.id=essayimage.id and user.like.userId='${id}' group by user.like.passageId;`
    let [rows]=await con.execute(sql);
    for(let item of rows){
        let Time = moment(item.likeTime).format('YYYY/MM/DD HH:mm');
        item.likeTime = Time;
    }
    return rows
}

async function changePwd(id,cipher){
    const sql=`update user set cipher='${cipher}' WHERE id='${id}';`;
    log(sql)
    let [rows]=await con.execute(sql);
    return rows
}

async function findByName(name){
    const sql=`select id,username,image from user where username like '%${name}%';`;
    let [rows]=await con.execute(sql);
    return rows
}

async function uploadpic(file){
    // console.log(file)
    let res='未选择文件';
    let dir=path.resolve(__dirname,'..');
    if(file.mimetype=='image/png'||file.mimetype=='image/jpeg'){
        const reader=fs.createReadStream(file.filepath);
        let filePath=dir+path.join('/upload')+`/${file.newFilename}`;
        const upStream=fs.createWriteStream(filePath);
        reader.pipe(upStream);
          res=file.newFilename
        }
      else{
        res='上传失败，为非图片文件'
      }
      return res;
    }

    async function changehead(id,path){
        const sql=`update user set image='${path}' where id='${id}'`;
        let [rows]=await con.execute(sql);
        return rows
    }

    async function changebg(id,path){
        const sql=`update user set background='${path}' where id='${id}'`;
        let [rows]=await con.execute(sql);
        return rows
    }

    async function getHelp(){
        const sql=`select * from help`;
        let [rows]=await con.execute(sql);
        return rows
    }

module.exports={
    changeUserInfo,
    interestme,
    getotherhome,
    changeattention,
    addattention,
    delattention,
    getLike,
    changePwd,
    findByName,
    uploadpic,
    changehead,
    changebg,
    getHelp
}